﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;


namespace IHISCETeim.DAL
{
    public class FTradeReportDAL
    {
        /// <summary>
        /// 获取应收帐宽所有的经销商
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTable GetALLJinXiaoShang(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport_GetALLJinXiaoShang", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 获取帐龄1 对应的最小最大到期日，用于显示列
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns>maxDaoQiRi,minDaoQiRi</returns>
        public static DataTable GetMinMaxDaoQiRi(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport_GetMinMaxDaoQiRi", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 帐龄报表1 
        /// 获取帐龄明细中的到期的金额（没有扣到已付的)
        /// 根据查询条件获取所有的相关信息，C#程序应通过再次配对进行汇总输出
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTable Report1GetAgeDetailJinE(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport1_GetAgeDetailJinE", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 帐龄报表1 按经销商导出
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTableCollection Report1GetJingXiaoShang(string JingXiaoShang, int Type)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport1_GetJingXiaoShang", JingXiaoShang, Type);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 帐龄报表2
        /// 应收帐款账龄表：（根据延期后到期日分析）
        /// </summary>
        /// <returns></returns>
        public static DataTableCollection Report2GetReportDate(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport2_GetReportDate", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 帐龄报表3
        /// 应收帐款账龄表：（根据初始到期日分别分析）
        /// </summary>
        /// <returns></returns>
        public static DataTableCollection Report3GetReportDate()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport3_GetReportDate");
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 报表4 应收帐款回款指标
        /// </summary>
        /// <returns></returns>
        public static DataTableCollection Report4GetReportDate(string GetDateFrom, string GetDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport4_GetReportDate", GetDateFrom, GetDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }


        /// <summary>
        /// 报表6.状况表
        /// </summary>
        /// <returns></returns>
        public static DataTableCollection Report6GetReportDate(string sqlwhere, string GetDateFrom, string GetDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport6_GetReportDate",sqlwhere, GetDateFrom, GetDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 报表5.逾期利息
        /// </summary>
        /// <returns></returns>
        public static DataTableCollection Report5GetReportDate(decimal Rate, DateTime lastDaoQiRi, string JingXiaoShang)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport5_GetReportDate", Rate, lastDaoQiRi, JingXiaoShang);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 报表7.奖金扣罚表
        /// 只出到期日所在的年份
        /// </summary>
        /// <returns></returns>
        public static DataTableCollection Report7GetReportDate(string sqlwhere, string lastDaoQiRi)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport7_GetReportDate",sqlwhere, lastDaoQiRi);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 报表8.分期逾期汇总
        /// </summary>
        /// <param name="lastDaoQiRi">上个月最后一天</param>
        /// <returns></returns>
        public static DataTableCollection Report8GetReportDate(string wheresql, string lastDaoQiRi)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport8_GetReportDate",wheresql, lastDaoQiRi);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 导出报表：还款提示函。获取所有 还有未收款项的合同信息
        /// </summary>
        /// <param name="datefrom"></param>
        /// <param name="dateto"></param>
        /// <returns></returns>
        public static DataTable FTradeExportGetHKTSHDetail(string datefrom,string dateto)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetHKTSHDetail", datefrom,dateto);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 导出资金走向说明
        /// 1.表0－－〉有充值的经销商
        /// 2.表1－－〉收款明细
        /// 3.表2－－〉充值明细
        /// </summary>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTableCollection FTradeExportGetZJZXSM(string fromdate,string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetZJZXSM", fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 导出收款收据
        /// </summary>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTableCollection FTradeExportGetSKSJ(string fromdate, string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetSKSJ", fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 导出融资租赁款结算确认书
        ///  1.表0－－〉有充值的经销商
        /// 2.表1－－〉充值明细
        /// </summary>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTableCollection FTradeExportGetRZZRKZJQRS(string fromdate, string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetRZZRKZJQRS", fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 导出发票签收单
        /// </summary>
        /// <param name="datefrom"></param>
        /// <param name="dateto"></param>
        /// <returns></returns>
        public static DataTableCollection FTradeExportGetInvoice(string datefrom, string dateto)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetInvoice", datefrom, dateto);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 导出发票信息
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTableCollection FTradeExportGetInvoiceInfo(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetInvoiceInfo", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 导入 返还利息
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTableCollection GetFanHuanLiXi(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetFanHuanLiXi", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 获取维护保导出数据
        /// </summary>
        /// <param name="WhereSql"></param>
        /// <returns></returns>
        public static DataTableCollection GetUpKeep(string WhereSql)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetUpKeep");
            DB.AddInParameter(dbCommand, "sqlwhere", DbType.String, WhereSql);

            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            return dt;
        }

        public static DataTableCollection GetYuanQi(string WhereSql)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeExport_GetYuanQi");
            DB.AddInParameter(dbCommand, "sqlwhere", DbType.String, WhereSql);

            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            return dt;
        }
        /// <summary>
        /// 应收款利润报表
        ///TYPE为1:获取总表
        ///type为2:获取明细表
       /// type为3:获取短缺成本明细表
        /// </summary>
        /// <param name="WhereSql"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static DataTableCollection Report9GetReportDate(string WhereSql,int type)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeReport9_GetReportDate");
            DB.AddInParameter(dbCommand, "sqlwhere", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "type", DbType.Int32, type);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            return dt;
        }
    }
}
